package com.apes.hr.transfer.repository;

import com.apes.framework.jpa.repository.CustomRepository;

import com.apes.hr.transfer.model.TransferApplication;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.Date;
import java.util.List;

/**
 * @author Fengjiajing
 * @create 2018-07-05 10:07
 */
public interface TransferApplicationRepository extends CustomRepository<TransferApplication, String>{

    @Query(value = " SELECT A.POST_ID, " +
            "(SELECT C.NAME FROM AUT_POSTS C WHERE C.ID = A.POST_ID) POST_MAME  FROM AUT_POST_USERS A, AUT_USERS B, MAS_PARTY_ROLE D   " +
            "WHERE A.USER_ID  = B.ID AND B.PERSON_ID = D.ID AND  D.PARTY_ID = ?1 " , nativeQuery = true)
    List qryEmployeePosts(Integer aId);

    @Query(value = "SELECT COUNT(t.id) FROM HR_TRANSFER_APPLICATION A, HR_TRANSFER_APPLICATION_ITEM T WHERE T.TRANSFER_APPLICATION_ID = A.ID " +
            "AND A.TRANSFER_TYPE = 000103 AND T.ICON_EXECUTE = 1 AND T.TRANSFER_UID = ?1 AND T.REPEAL = 0 AND A.STATE != 'delete'" +
            "AND t.effective_date >= ?3 and t.effective_date <= ?2" ,nativeQuery = true)
    int findTransferNum(String employee, Date today, Date oldDay);

    @Query("select s " +
            "from TransferApplication s where s.hrTransferType ='000103' and s.lastUpdatedStamp > :times or (s.id > :id and s.lastUpdatedStamp = :times)  " +
            " order by s.lastUpdatedStamp, s.id asc ")
    List<TransferApplication> findAllTransferApplicationByTs(@Param("times") Date times, @Param("id") String id, Pageable pageable);
}
